
/**
	Author	     :  Basha
	Date	     :  Sep 29th, 2004
 	Description  :  This proc returns all the line items in order to generate Sales Invoice for given search criteria
	Usage        :  exec sp_get_line_items_for_sales_invoice '','','','',0,0 
**/

alter proc sp_get_line_items_for_sales_invoice
	@packingSlipLabel varchar(50),
	@salesOrder_no    varchar(50),
	@year		  varchar(10),
	@customer_po	  varchar(50),
	@customer_id	  int,
	@address_id	  int	
as

begin
	declare @cslit_id            int
	declare @customerShipping_id int
	declare @label               varchar(50)
	declare @salesOrder_number   varchar(50)
	declare @cslit_qty_shipped   int
	declare @cool_unit_cost      float
	declare @cool_requested_date datetime
	declare @part_number         varchar(50)
	declare @p_comments          varchar(100)
	declare @gp_location	     varchar(50)
	declare @customerName        varchar(100)

	declare @results Table(CSLIT_ID int, CUSTOMERSHIPPING_ID int, LABEL varchar(50), SALESORDER_NO varchar(50), CSLIT_Qty_Shipped int, COOL_UNIT_COST float, COOL_Requested_Date datetime, PART_NUMBER varchar(50), P_COMMENTS varchar(1000),GP_LOCATION varchar(50), CUSTOMER_NAME varchar(100)) 
	if @customer_id <> 0 AND  @address_id <> 0
	begin	
		declare lineItemCursor CURSOR FOR
		SELECT CSLIT.CSLIT_ID, CST.CUSTOMERSHIPPING_ID, CST.LABEL, SO.SALESORDER_NO, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_UNIT_COST, COOL.COOL_Requested_Date, P.PART_NUMBER, P.P_COMMENTS , (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER.CUSTOMER_ID = SO.CUSTOMER_ID )
		FROM PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT 
		WHERE P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1 AND CST.SALESORDER_ID = SO.SALESORDER_ID AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT.Invoice_Generated = 1 and CSLIT.CSLIT_Status = 'SHIPPED' and CST.Label like @packingSlipLabel + '%' AND SO.SALESORDER_NO like @salesOrder_no + '%' AND YEAR(SO.SO_BOOKING_DATE) like @year + '%' AND SO.SO_CUSTOMER_PO_NUMBER like @customer_po + '%' AND SO.CUSTOMER_ID = @customer_id AND (CST_From_Address_ID = @address_id OR CST_Location_Address_ID = @address_id) order by SO.SALESORDER_NO, CSLIT.CustomerShipping_ID , CSLIT.LineItem_ID
	end
	else if @customer_id <> 0 AND  @address_id = 0
	begin	
		declare lineItemCursor CURSOR FOR
		SELECT CSLIT.CSLIT_ID, CST.CUSTOMERSHIPPING_ID, CST.LABEL, SO.SALESORDER_NO, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_UNIT_COST, COOL.COOL_Requested_Date, P.PART_NUMBER, P.P_COMMENTS , ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER.CUSTOMER_ID = SO.CUSTOMER_ID  )
		FROM PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT 
		WHERE P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1 AND CST.SALESORDER_ID = SO.SALESORDER_ID AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT.Invoice_Generated = 1 and CSLIT.CSLIT_Status = 'SHIPPED' and CST.Label like @packingSlipLabel + '%' AND SO.SALESORDER_NO like @salesOrder_no + '%' AND YEAR(SO.SO_BOOKING_DATE) like @year + '%' AND SO.SO_CUSTOMER_PO_NUMBER like @customer_po + '%' AND SO.CUSTOMER_ID = @customer_id order by SO.SALESORDER_NO, CSLIT.CustomerShipping_ID , CSLIT.LineItem_ID
	end
	else if @customer_id = 0 AND  @address_id <> 0
	begin	
		declare lineItemCursor CURSOR FOR
		SELECT CSLIT.CSLIT_ID, CST.CUSTOMERSHIPPING_ID, CST.LABEL, SO.SALESORDER_NO, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_UNIT_COST, COOL.COOL_Requested_Date, P.PART_NUMBER, P.P_COMMENTS , ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER.CUSTOMER_ID = SO.CUSTOMER_ID  )
		FROM PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT 
		WHERE P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1 AND CST.SALESORDER_ID = SO.SALESORDER_ID AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT.Invoice_Generated = 1 and CSLIT.CSLIT_Status = 'SHIPPED' and CST.Label like @packingSlipLabel + '%' AND SO.SALESORDER_NO like @salesOrder_no + '%' AND YEAR(SO.SO_BOOKING_DATE) like @year + '%' AND SO.SO_CUSTOMER_PO_NUMBER like @customer_po + '%' AND (CST_From_Address_ID = @address_id OR CST_Location_Address_ID = @address_id) order by SO.SALESORDER_NO, CSLIT.CustomerShipping_ID , CSLIT.LineItem_ID
	end
	else
	begin	
		declare lineItemCursor CURSOR FOR
		SELECT CSLIT.CSLIT_ID, CST.CUSTOMERSHIPPING_ID, CST.LABEL, SO.SALESORDER_NO, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_UNIT_COST, COOL.COOL_Requested_Date, P.PART_NUMBER, P.P_COMMENTS , ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER.CUSTOMER_ID = SO.CUSTOMER_ID  )
		FROM PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT 
		WHERE P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1 AND CST.SALESORDER_ID = SO.SALESORDER_ID AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT.Invoice_Generated = 1 and CSLIT.CSLIT_Status = 'SHIPPED' and CST.Label like @packingSlipLabel + '%' AND SO.SALESORDER_NO like @salesOrder_no + '%' AND YEAR(SO.SO_BOOKING_DATE) like @year + '%' AND SO.SO_CUSTOMER_PO_NUMBER like @customer_po + '%' order by SO.SALESORDER_NO, CSLIT.CustomerShipping_ID , CSLIT.LineItem_ID
	end
	open lineItemCursor 

 	fetch next from lineItemCursor into @cslit_id, @customerShipping_id, @label, @salesOrder_number, @cslit_qty_shipped, @cool_unit_cost, @cool_requested_date, @part_number, @p_comments , @customerName          
 	while @@fetch_status = 0
 	begin
	
		exec sp_get_ps_line_item_gp_location @cslit_id , @gp_location out
		
		insert into @results (CSLIT_ID, CUSTOMERSHIPPING_ID, LABEL, SALESORDER_NO, CSLIT_Qty_Shipped, COOL_UNIT_COST, COOL_Requested_Date, PART_NUMBER, P_COMMENTS, GP_LOCATION, CUSTOMER_NAME) 
		values ( @cslit_id, @customerShipping_id, @label, @salesOrder_number, @cslit_qty_shipped, @cool_unit_cost, @cool_requested_date, @part_number, @p_comments, @gp_location , @customerName )

		set @cslit_id = null
		set @customerShipping_id = null
		set @label = null
		set @salesOrder_number = null
		set @cslit_qty_shipped = null
		set @cool_unit_cost = null
		set @cool_requested_date = null
		set @part_number = null
		set @p_comments = null 
		set @customerName = null        

 		fetch next from lineItemCursor into @cslit_id, @customerShipping_id, @label, @salesOrder_number, @cslit_qty_shipped, @cool_unit_cost, @cool_requested_date, @part_number, @p_comments , @customerName         
 	end

 	close lineItemCursor
 	deallocate lineItemCursor

	select * from @results order by SALESORDER_NO, GP_LOCATION, CUSTOMERSHIPPING_ID
end